package com.hbnu.dao.impl;

import com.hbnu.dao.DormManagerDao;
import com.hbnu.pojo.DormBuild;
import com.hbnu.util.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;

/**
 * @author chendikai
 * @date 2024-11-13 下午 3:16
 */
public class DormManagerDaoImpl implements DormManagerDao {
    @Override
    public List<DormBuild> find() {
        //获取连接（数据库地址、用户名、密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();

            //准备sql语句
            String sql = "select * from tb_dormbuild";
            //获取集装箱
            preparedStatement = connection.prepareStatement(sql);
            //执行sql语句
            resultSet = preparedStatement.executeQuery();

            List<DormBuild> builds = new ArrayList<DormBuild>();
            while (resultSet.next()) {
                DormBuild build = new DormBuild();
                build.setId(resultSet.getInt("id"));
                build.setName(resultSet.getString("name"));
                build.setRemark(resultSet.getString("remark"));
                build.setDisabled(resultSet.getInt("disabled"));

                builds.add(build);

            }
            return builds;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public List<DormBuild> findByUserId(Integer userId) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<DormBuild> builds = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();

            String sql = "SELECT tb_dormbuild.* FROM tb_manage_dormbuild LEFT JOIN tb_dormbuild ON " +
                    "tb_manage_dormbuild.dormBuild_id = tb_dormbuild.id " +
                    "WHERE tb_manage_dormbuild.user_id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, userId);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                DormBuild dormBuild = new DormBuild();

                dormBuild.setId(resultSet.getInt("id"));
                dormBuild.setName(resultSet.getString("name"));
                dormBuild.setDisabled(resultSet.getInt("disabled"));
                dormBuild.setRemark(resultSet.getString("remark"));

                builds.add(dormBuild);
            }

            return builds;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 资源关闭
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public void saveManagerAndBuild(Integer id, String[] dormBuildIds) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "INSERT INTO tb_manage_dormbuild(user_id,dormbuild_id) VALUES(?,?)";

            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            for (String dormBuildId : dormBuildIds) {
                preparedStatement.setInt(1, id);
                preparedStatement.setInt(2, Integer.parseInt(dormBuildId));

                // 将sql语句添加到批处理
                preparedStatement.addBatch();
            }

            // 批处理
            preparedStatement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, preparedStatement, connection);
        }
    }

    @Override
    public void deleteByUserId(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "delete from tb_manage_dormbuild where user_id=?";

            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            preparedStatement.setInt(1, id);

            preparedStatement.executeUpdate();

            resultSet = preparedStatement.getGeneratedKeys();
            resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<DormBuild> findAll() {
        // ① 获取连接（数据库地址 用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();
// ② 准备SQL语句
            String sql = "select * from tb_dormbuild ";
// ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
// ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
// 因为查询出来的结果包括表头信息，所以要指针下移一行，看是否有查询出来的数据
// 如有数据，就进入循环体，封装该行数据
            List<DormBuild> builds = new ArrayList<>();
            while (rs.next()) {
                DormBuild build = new DormBuild();
                build.setId(rs.getInt("id"));
                build.setName(rs.getString("name"));
                build.setDisabled(rs.getInt("disabled"));
                build.setRemark(rs.getString("remark"));
                builds.add(build);
            }
            return builds;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public DormBuild findById(int id) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtils.getConnection();

            String sql="select * from tb_dormbuild where id=?";
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);

            resultSet= preparedStatement.executeQuery();

            while(resultSet.next()){
                DormBuild dormBuild=new DormBuild();
                dormBuild.setId(resultSet.getInt("id"));
                dormBuild.setName(resultSet.getString("name"));
                dormBuild.setDisabled(resultSet.getInt("disabled"));
                dormBuild.setRemark(resultSet.getString("remark"));

                return dormBuild;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet,preparedStatement,connection);
        }
        return null;
    }

    @Override
    public DormBuild findByName(String name) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtils.getConnection();

            String sql="select * from tb_dormbuild where name=?";
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,name);

            resultSet= preparedStatement.executeQuery();

            while(resultSet.next()){
                DormBuild dormBuild=new DormBuild();
                dormBuild.setId(resultSet.getInt("id"));
                dormBuild.setName(resultSet.getString("name"));
                dormBuild.setDisabled(resultSet.getInt("disabled"));
                dormBuild.setRemark(resultSet.getString("remark"));

                return dormBuild;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet,preparedStatement,connection);
        }

        return null;
    }

    @Override
    public void update(DormBuild dormBuild) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection=DBUtils.getConnection();

            String sql="update tb_dormbuild set name=?,remark=?,disabled=? where id=?";
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1, dormBuild.getName());
            preparedStatement.setString(2, dormBuild.getRemark());
            preparedStatement.setInt(3,dormBuild.getDisabled());
            preparedStatement.setInt(4,dormBuild.getId());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(null,preparedStatement,connection);
        }

    }

    @Override
    public void save(DormBuild build) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection=DBUtils.getConnection();

            String sql="insert into tb_dormbuild(name,remark,disabled) values(?,?,?)";

            preparedStatement= connection.prepareStatement(sql);

            preparedStatement.setString(1, build.getName());
            preparedStatement.setString(2, build.getRemark());
            preparedStatement.setInt(3,build.getDisabled());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(null,preparedStatement,connection);
        }

    }
}
